from flask import Blueprint, render_template, request, redirect, url_for, flash, session, g, jsonify
from app.form import LoginForm, RegistForm
from app.models import User, Car, CarRecord, elec_Car, Invoice
from flask_login import login_user, logout_user, login_required
from sqlalchemy import and_
from .utils import farmat_data
from exts import db
import flask_excel as excel
import datetime, config, time

app_blueprint = Blueprint('app', __name__)


@app_blueprint.route('/')
# @login_required
def index():
    users = User.query.order_by(User.join_time.asc()).all()
    content = {
        'users': users,
    }
    return render_template('index.html', **content)


@app_blueprint.route('/exp_excel/', methods=['GET'])
# @login_required
def exp_excel():
    q = db.session.query(
        User.username.label('用户名'),
        User.email.label('邮箱'),
        User.enabled.label('状态'),
        User.join_time.label('注册时间')
    ).order_by(User.join_time.asc())
    query_sets = q.all()
    return excel.make_response_from_query_sets(
        query_sets,
        column_names=[
            '用户名',
            '邮箱',
            '状态',
            '注册时间',
        ],
        file_type='xlsx',
        file_name='list.xlsx'
    )


# 汽车管理首页
@app_blueprint.route('/ceshi/', methods=['GET', 'POST'])
def ceshi():
    # 计算当周的日期
    a = time.strftime('%u', time.localtime(time.time()))
    today = datetime.datetime.today()
    todays = str(today).split(' ')[0]
    # print(todays)
    start_day = today - datetime.timedelta(days=(int(a) - 1))
    day_list = []
    day_list.append(str(start_day).split(' ')[0])
    for i in range(6):
        start_day += datetime.timedelta(days=1)
        day_list.append(str(start_day).split(' ')[0])
    # 获取汽车列表，填充select
    cars = Car.query.order_by(Car.id.asc()).all()

    # start汽车记录信息查询-----------------------------
    q = db.session.query(
        CarRecord.id,
        CarRecord.write_time.label('记录时间'),
        CarRecord.kilom.label('公里数'),
        CarRecord.than_yest.label('较上日'),
        CarRecord.oil_cost.label('加油费用'),
        CarRecord.car_id,
        CarRecord.car_code,
        CarRecord.record_day # 记录的日期
    )
    # 汽车记录
    # records = q.filter(and_(CarRecord.record_day >= start_day,CarRecord.record_day <= end_day)).order_by(CarRecord.record_day.asc()).all()
    records = q.all()
    records = farmat_data(records)
    records = sorted(records, key=lambda keys: keys['day'], reverse=True)
    # start电度表查询记录-----------------------------------------------------------------------------
    elec = elec_Car.query.all()
    elecs = farmat_data(elec)
    elecs = sorted(elecs, key=lambda keys: keys['day'], reverse=True)
    # print(result)
    # start发票数据-------------------------------------------------------------------------------------
    invoices = Invoice.query.all()
    invoices = farmat_data(invoices)
    invoices = sorted(invoices, key=lambda keys: keys['day'], reverse=True)
    # print(invoices)
    content = {
        'cars':cars,
        'records' : records,
        'elecs': elecs,
        'invoices':invoices
    }
    return render_template('ceshi2.html', **content)


# 添加汽车的记录信息
@app_blueprint.route('/addInfo/', methods=['POST'])
# @login_required
def addInfo():
    car_id = int(request.form.get('car_id'))
    car = Car.query.get(car_id)
    print(car.car_code)
    write_time = request.form.get('write_time')
    kilom = int(request.form.get('kilom'))
    than_yest = int(request.form.get('than_yest'))
    oil_cost = int(request.form.get('oil_cost'))
    record_day = request.form.get('record_day')
    if record_day:
        record = CarRecord(write_time=write_time, kilom=kilom,record_day=record_day, than_yest=than_yest, oil_cost=oil_cost, car_id=car_id, car_code=car.car_code)
    else:
        record = CarRecord(write_time=write_time, kilom=kilom, than_yest=than_yest, oil_cost=oil_cost, car_id=car_id, car_code=car.car_code)
    db.session.add(record)
    db.session.commit()
    return redirect(url_for('app.ceshi'))

@app_blueprint.route('/alterInfo/', methods=['POST'])
# @login_required
def alterInfo():
    car_code = request.form.get('car_code')
    car_id = request.form.get('car_id')
    write_time = request.form.get('write_time')
    kilom = int(request.form.get('kilom'))
    than_yest = int(request.form.get('than_yest'))
    oil_cost = int(request.form.get('oil_cost'))
    record_day = request.form.get('record_day')
    info = CarRecord.query.get(car_id)
    if info:
        info.write_time = write_time
        info.kilom = kilom
        info.than_yest = than_yest
        info.oil_cost = oil_cost
        info.record_day = record_day
        db.session.commit()
    return redirect(url_for('app.ceshi'))



# 删除汽车记录信息的视图
@app_blueprint.route('/delInfo/<int:id>')
# @login_required
def delInfo(id):
    record_id = id
    record = CarRecord.query.get(record_id)
    if record:
        db.session.delete(record)
        db.session.commit()
    return redirect(url_for('app.ceshi'))


# 添加汽车
@app_blueprint.route('/addCar/', methods=['POST'])
# @login_required
def addCar():
    car_code = request.form.get('car_code')
    car_type = request.form.get('car_type')
    # print(car_code, car_type)
    # todo:待改进：需要进行form验证，检测是否有重复车牌
    car = Car.query.filter(Car.car_code==car_code).first()
    if car:
        pass
    else:
        car = Car(car_code=car_code, car_type=car_type)
        db.session.add(car)
        db.session.commit()

    return redirect(url_for('app.ceshi'))

# 删除汽车
@app_blueprint.route('/delCar/<int:id>')
# @login_required
def delCar(id):
    car_id = id
    car = Car.query.filter(Car.id==car_id).first()
    if car:
        db.session.delete(car)
        db.session.commit()
    return redirect(url_for('app.ceshi'))


# 修改汽车的视图
@app_blueprint.route('/alterCar/', methods=['POST'])
# @login_required
def alterCar():
    car_id = request.form.get('car_id')
    car_code = request.form.get('car_code')
    car_type = request.form.get('car_type')
    car = Car.query.filter(Car.id==car_id).first()
    if car:
        car.car_code = car_code
        car.car_type = car_type
        db.session.commit()
    return redirect(url_for('app.ceshi'))


# 添加电度表信息
@app_blueprint.route('/addElecCar/', methods=['POST'])
# @login_required
def addElecCar():
    day_type = request.form.get('day_type')
    record_day = request.form.get("record_day")
    sum_elec = request.form.get('sum_elec')
    xiaomaibu = request.form.get('xiaomaibu')
    dianzhuang = request.form.get('dianzhuang')
    record_man = request.form.get('record_man')
    # todo: 缺少一个验证，防止重复添加信息。计划以后在form验证中进行检测。
    elec = elec_Car(record_day=record_day,day_type=day_type,sum_elec=sum_elec,xiaomaibu=xiaomaibu,dianzhuang=dianzhuang,record_man=record_man)
    db.session.add(elec)
    db.session.commit()
    return redirect(url_for('app.ceshi'))


@app_blueprint.route('/alterElec/', methods=['POST'])
# @login_required
def alterElec():
    elec_id = request.form.get('elec_id')
    day_type = request.form.get('day_type')
    record_day = request.form.get("record_day")
    sum_elec = request.form.get('sum_elec')
    xiaomaibu = request.form.get('xiaomaibu')
    dianzhuang = request.form.get('dianzhuang')
    record_man = request.form.get('record_man')
    elec = elec_Car.query.get(elec_id)
    if elec:
        elec.day_type = day_type
        elec.record_day = record_day
        elec.sum_elec = sum_elec
        elec.xiaomaibu = xiaomaibu
        elec.dianzhuang = dianzhuang
        elec.record_man = record_man
        db.session.commit()
    return redirect(url_for('app.ceshi'))


@app_blueprint.route('/delElecCar/<int:id>')
# @login_required
def delElecCar(id):
    elec_id = id
    elec_car = elec_Car.query.get(elec_id)
    if elec_car:
        db.session.delete(elec_car)
        db.session.commit()
    return redirect(url_for('app.ceshi'))


@app_blueprint.route('/addInvoice/', methods=['POST'])
# @login_required
def addInvoice():
    record_time = request.form.get('record_time')
    record_day = request.form.get('record_day')
    invoice_code = request.form.get('invoice_code')
    coun = request.form.get('coun')
    record_man = request.form.get('record_man')
    invoice = Invoice(record_day=record_day, record_time=record_time, invoice_code=invoice_code, coun=coun,record_man=record_man)
    db.session.add(invoice)
    db.session.commit()
    return redirect(url_for('app.ceshi'))

@app_blueprint.route('/alterInvoice/', methods=['POST'])
# @login_required
def alterInvoce():
    invoice_id = request.form.get('invoice_id')
    record_time = request.form.get('record_time')
    record_day = request.form.get('record_day')
    invoice_code = request.form.get('invoice_code')
    coun = request.form.get('coun')
    record_man = request.form.get('record_man')
    invoice = Invoice.query.get(invoice_id)
    if invoice:
        invoice.id = invoice_id
        invoice.record_time = record_time
        invoice.record_day = record_day
        invoice.invoice_code = invoice_code
        invoice.coun = coun
        invoice.record_man = record_man
        db.session.commit()
    return redirect(url_for('app.ceshi'))



@app_blueprint.route('/delInvoice/<int:id>')
# @login_required
def delInvoice(id):
    invoice = Invoice.query.get(id)
    if invoice:
        db.session.delete(invoice)
        db.session.commit()
    return redirect(url_for('app.ceshi'))




@app_blueprint.route('/login/', methods=['GET', 'POST'])
def login():
    form = LoginForm()
    if form.validate_on_submit():
        user = User.query.filter_by(email=form.email.data).first()
        rem = False
        # print(form.switch.data,type(form.switch.data))
        if form.switch.data == 'on':
            rem = True
        # print(rem)
        login_user(user, remember=rem)
        session['email'] = user.email
        return redirect(url_for('app.index'))
    return render_template('login.html', form=form)


@app_blueprint.route('/regist/', methods=['GET', 'POST'])
def regist():
    form = RegistForm()
    # print(form.username.data)
    if form.validate_on_submit():
        user = User()
        user.email = form.email.data
        user.username = form.username.data
        user.password = form.password.data
        db.session.add(user)
        db.session.commit()
        return redirect(url_for('app.login'))

    return render_template('regist.html', form=form)


@app_blueprint.route('/logout/', methods=['GET', 'POST'])
def logout():
    logout_user()
    return redirect(url_for('app.login'))


@app_blueprint.before_request
def before_request():
    if 'email' in session:
        user = User.query.filter_by(email=session.get('email')).first()
        if user:
            g.user = user
            # print(g.user.username)
